﻿
CREATE PROCEDURE [dbo].[resuma_CalculSpor] 
	@Anul smallint,
	@Luna smallint,
	@CodUnitate smallint,
	@IDPersoanaSelectata int
AS

DECLARE @MesajEroare varchar(500)
DECLARE @prcVech    float
DECLARE @Salariu    DECIMAL(18,2)
DECLARE @Norma      integer
DECLARE @OreAcord   integer
DECLARE @OrePontate integer

DECLARE @IDPersoana int
DECLARE @PrcFctSupl decimal(5,2)
DECLARE @SumFctSupl decimal(18,2)
DECLARE @AreSpVech  char(1)
DECLARE @AreSpFctS  char(1)
DECLARE @IDDepartament int
declare @OP decimal (18,2)
declare @op_spor decimal(5,2)
declare @OPP integer
declare @IdTipSpor smallint
declare @valoare decimal(18,2)
declare @Format char(1)
declare @ConcediuOdihna char(1)
declare @c_boala char(1)
declare @c_ore_supl char(1)
declare @e_permanent char(1)
DECLARE @Salar_orar decimal(18,2)
DECLARE @Norma_redusa  integer
declare @ore_lucrate integer
DECLARE @valoare_spor decimal(18,2)
declare @valoare_spor_o decimal(18,2)
declare @valoare_spor_int decimal(18,2)
declare @ore_spor integer
declare @aa_vech integer
declare @baze varchar(2)
declare @dif integer
declare @ol integer

declare @ZI_COB integer
declare @ZI_NEMO integer
declare @ZI_OBL_PL integer
declare @ORE_COB integer
declare @OZI_OBL_PL integer
declare @OPS integer
declare @ZI_ODH integer
declare @OZI_ODH integer
declare @wore_l integer
declare @oo integer
declare @KACORD decimal(5,2)
declare @O_CFS integer
declare @ZI_OBL_NPL integer
declare @OZI_OBL_NPL integer
declare @ore_total integer
declare @pagina integer


/*==== VARIABILE PENTRU BUGETARI*/

declare @SalarCfGrilei decimal(18,2)
declare @SalarBaza decimal(18,2)
declare @BazaVech decimal(18,2)
declare @SporVechime decimal(18,2)
declare @baza_spor decimal(18,2)
DECLARE @OreNorma smallint
DECLARE @FractiuneNorma decimal(5,2)
DECLARE @IDAngajatSalarizare int
DECLARE @IDOcupatie int

select @op = ZileLucratoare from [resuma_SetariLuna] WHERE Luna=@Luna AND Anul=@Anul

IF @IDPersoanaSelectata=-1
	DELETE FROM  CalculSporuri where Luna=@Luna AND Anul=@Anul
ELSE
	DELETE FROM CalculSporuri WHERE [IDPersoana] = @IDPersoanaSelectata AND Luna=@Luna AND Anul=@Anul


-- ----------------------------------------- CALCUL SPORURI BUGETARI ---------------------------------------------


-- ************************ CAlCUL SPORURI BUGETARI CARE INTRA IN SALAR CF.GRILEI (intra_in='SG') ******************
DECLARE  crsSpor SCROLL CURSOR FOR 
SELECT DISTINCT 
	Ps.IDPersoana,Ans.IDAngajatSalarizare,Ans.IDDepartament,pss.IDTipSpor,Ans.Salar,
	ts.BazaCalculSpor,ts.valoare,ts.Format,ts.ConcediuOdihna,Ans.OreNorma,Ans.FractiuneNorma,AnO.IDOcupatie
	FROM  	
	    [Persoane] Ps 
	    JOIN [AngajatiSalarizare] Ans ON Ps.IdPersoana = Ans.IdPersoana
	    JOIN [AngajatiOcupatii] AnO ON Ps.IdPersoana = AnO.IdPersoana
	    JOIN [AngajatiSporuri] Pss ON Ps.IdPersoana = Pss.IdPersoana
		JoiN [TipSpor] ts ON Pss.IDTipSpor = ts.IDTipSpor
	WHERE   
	Ps.CodUnitate=@CodUnitate and ts.IntraIn='SG'  and ts.Format<>'G' AND
	YEAR(Pss.DeLaData)*100+MONTH(Pss.DeLaData)<=@Anul*100+@luna 
	AND (Pss.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(Pss.PanaLaData)*100+MONTH(Pss.PanaLaData))
	AND (AnO.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(AnO.PanaLaData)*100+MONTH(AnO.PanaLaData))
	AND (Ps.DataPlecarii IS NULL OR @Anul*100+@Luna<=YEAR(Ps.DataPlecarii)*100+MONTH(Ps.DataPlecarii))
	AND (Ps.CodUnitate=@CodUnitate) 
	AND(@IDPersoanaSelectata=-1 OR Ps.IDPersoana = @IDPersoanaSelectata)


OPEN crsSpor
FETCH first from  crsSpor INTO @IDPersoana,@IDAngajatSalarizare,@IDDepartament,@IDTipSpor,@salariu,@baze,@valoare,@Format,@ConcediuOdihna,@OreNORMA,@FractiuneNorma,@IDOcupatie


WHILE @@fetch_status = 0  
BEGIN

  SELECT @ore_lucrate=isnull((select sum(Pt.Valoare)
  FROM AngajatiPontaj Pt JOIN [TipPrezenta] Tp  ON Pt.IdTipPrezenta = Tp.IdTipPrezenta 
  WHERE  
	Pt.IdPersoana = @IDPersoana AND 
   	Pt.IdAngajatSalarizare=@IDAngajatSalarizare and
   	Tp.TIP = 'P' AND Pt.Luna=@Luna AND Pt.Anul=@Anul),0)

  SELECT @ZI_ODH=isnull((select sum(pt.valoare)
  FROM AngajatiPontaj Pt JOIN [TipPrezenta] Tp  ON Pt.IdTipPrezenta = Tp.IdTipPrezenta 
  WHERE 
	Pt.IdPersoana = @IDPersoana AND 
	Pt.IdAngajatSalarizare=@IDAngajatSalarizare and
	Tp.TIP = 'O' AND Pt.Luna=@Luna AND Pt.Anul=@Anul),0)
	
  IF @Format='F'		
	SELECT @valoare = Valoare FROM [GrileOcupatii] WHERE [IDTipSpor]=@IdTipSpor AND [IDOcupatie]=@IDOcupatie		
  IF @Format='D'		
	SELECT @valoare = Valoare FROM [GrileOcupatiiDep] WHERE [IDTipSpor]=@IdTipSpor AND [IDOcupatie]=@IDOcupatie AND [IDDepartament]=@IDDepartament

	
	IF @ConcediuOdihna=1 
	begin
		set @valoare_spor=@salariu*(@ore_lucrate/(@OreNorma*@op*@FractiuneNorma))*@valoare/100
		set @valoare_spor_o=@salariu*(@ZI_ODH/(@op*@FractiuneNorma))*@valoare/100
	end
	ELSE
	begin
		set @valoare_spor=@salariu*@valoare/100
		set @valoare_spor_o=0
	end
		
	if @Format='S'
		IF @ConcediuOdihna=1 
		begin
			set @valoare_spor=@valoare*(@ore_lucrate/(@OreNorma*@op*@FractiuneNorma))
			set @valoare_spor_o=@valoare*(@ZI_ODH/(@op*@FractiuneNorma))
		end
		ELSE
		begin
			set @valoare_spor=@valoare
			set @valoare_spor_o=0
		end
		


	SET @valoare_spor_int=@salariu*@valoare/100
	UPDATE AngajatiSporuri SET [Procent]=@valoare, [Valoare]=ROUND(@valoare_spor_int,0) WHERE
		IdPersoana=@IDPersoana AND [IDTipSpor]=@IdTipSpor

	INSERT  INTO CalculSporuri 
		(IdPersoana, IdTipSpor, VALOARE, VALOARECO,VALOAREINT,PROCENT, IDAngajatSalarizare,Anul,Luna) 
     	 VALUES
		(@IDPersoana, @IdTipSpor,round (@valoare_spor,0),round (@valoare_spor_o,0), round (@valoare_spor_int,0),
		(case when @Format='S' then 0
			else @valoare
			end),@IDAngajatSalarizare,@Anul,@Luna)

	FETCH NEXT from crsSpor INTO @IDPersoana,@IDAngajatSalarizare,@IDDepartament,@IDTipSpor,@salariu,@baze,@valoare,@Format,@ConcediuOdihna,@OreNORMA,@FractiuneNorma,@IDOcupatie


END
CLOSE crsSpor
DEALLOCATE crsSpor


-- ************************ CAlCUL SPORURI BUGETARI CARE INTRA IN SALAR DE BAZA (intra_in = 'SB') ******************

DECLARE  crsSpor SCROLL CURSOR FOR 
SELECT DISTINCT 
	Ps.IDPersoana,Ans.IDAngajatSalarizare,Ans.IDDepartament,pss.IDTipSpor,Ans.Salar,
	ts.BazaCalculSpor,ts.valoare,ts.Format,ts.ConcediuOdihna,Ans.OreNorma,Ans.FractiuneNorma,AnO.IDOcupatie
	FROM  	
	    [Persoane] Ps 
	    JOIN [AngajatiSalarizare] Ans ON Ps.IdPersoana = Ans.IdPersoana
	    JOIN [AngajatiOcupatii] AnO ON Ps.IdPersoana = AnO.IdPersoana
	    JOIN [AngajatiSporuri] Pss ON Ps.IdPersoana = Pss.IdPersoana
		JoiN [TipSpor] ts ON Pss.IDTipSpor = ts.IDTipSpor
	WHERE   
	Ps.CodUnitate=@CodUnitate and ts.IntraIn='SB'  and ts.Format<>'G' AND
	YEAR(Pss.DeLaData)*100+MONTH(Pss.DeLaData)<=@Anul*100+@luna 
	AND (Pss.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(Pss.PanaLaData)*100+MONTH(Pss.PanaLaData))
	AND (Ps.DataPlecarii IS NULL OR @Anul*100+@Luna<=YEAR(Ps.DataPlecarii)*100+MONTH(Ps.DataPlecarii))
	AND (Ps.CodUnitate=@CodUnitate)
	AND(@IDPersoanaSelectata=-1 OR Ps.IDPersoana = @IDPersoanaSelectata)


OPEN crsSpor
FETCH first from  crsSpor INTO @IDPersoana,@IDAngajatSalarizare,@IDDepartament,@IDTipSpor,@salariu,@baze,@valoare,@Format,@ConcediuOdihna,@OreNORMA,@FractiuneNorma,@IDOcupatie

WHILE @@fetch_status = 0  
BEGIN

	SELECT @ore_lucrate=isnull((select sum(Pt.Valoare)
	FROM AngajatiPontaj Pt JOIN [TipPrezenta] Tp  ON Pt.IdTipPrezenta = Tp.IdTipPrezenta 
	WHERE  
	Pt.IdPersoana = @IDPersoana AND 
   	Pt.IdAngajatSalarizare=@IDAngajatSalarizare and
   	Tp.TIP = 'P' AND Pt.Luna=@Luna AND Pt.Anul=@Anul),0)

	SELECT @ZI_ODH=isnull((select sum(pt.valoare)
	FROM AngajatiPontaj Pt JOIN [TipPrezenta] Tp  ON Pt.IdTipPrezenta = Tp.IdTipPrezenta 
	WHERE 
	Pt.IdPersoana = @IDPersoana AND 
	Pt.IdAngajatSalarizare=@IDAngajatSalarizare and
	Tp.TIP = 'O' AND Pt.Luna=@Luna AND Pt.Anul=@Anul),0)

	IF @baze='SG'
		select @SalarCfGrilei=@salariu+isnull((select sum(s.ValoareInt) from 
		CalculSporuri s join TipSpor ts on s.IdTipSpor=ts.IDTipSpor
				where s.IdPersoana=@IDPersoana and Luna=@Luna AND Anul=@Anul AND
				ts.IntraIn='SG'),0)	
	else
		if @baze='SI'
			select @SalarCfGrilei=@salariu
		else
			select @SalarCfGrilei=0 
			
	IF @Format='F'		
		SELECT @valoare = Valoare FROM [GrileOcupatii] WHERE [IDTipSpor]=@IdTipSpor AND [IDOcupatie]=@IDOcupatie		
	IF @Format='D'		
		SELECT @valoare = Valoare FROM [GrileOcupatiiDep] WHERE [IDTipSpor]=@IdTipSpor AND [IDOcupatie]=@IDOcupatie AND [IDDepartament]=@IDDepartament


	IF @ConcediuOdihna=1 
	begin
		set @valoare_spor=@SalarCfGrilei*(@ore_lucrate/(@OreNorma*@op*@FractiuneNorma))*@valoare/100
		set @valoare_spor_o=@SalarCfGrilei*(@ZI_ODH/(@op*@FractiuneNorma))*@valoare/100
	end
	ELSE
	begin
		set @valoare_spor=@SalarCfGrilei*@valoare/100
		set @valoare_spor_o=0
	end
		
	if @Format='S'
		IF @ConcediuOdihna=1 
		begin
			set @valoare_spor=@valoare*(@ore_lucrate/(@OreNorma*@op*@FractiuneNorma))
			set @valoare_spor_o=@valoare*(@ZI_ODH/(@op*@FractiuneNorma))
		end
		ELSE
		begin
			set @valoare_spor=@valoare
			set @valoare_spor_o=0
		end
		

	select @valoare_spor_int=@SalarCfGrilei*@valoare/100
	UPDATE AngajatiSporuri SET [Procent]=@valoare, [Valoare]=ROUND(@valoare_spor_int,0) WHERE
		IdPersoana=@IDPersoana AND [IDTipSpor]=@IdTipSpor

	INSERT  INTO CalculSporuri 
		(IdPersoana, IdTipSpor, VALOARE, VALOARECO,VALOAREINT,PROCENT, IDAngajatSalarizare,Anul,Luna) 
     	 VALUES
		(@IDPersoana, @IdTipSpor,round (@valoare_spor,0),round (@valoare_spor_o,0), round (@valoare_spor_int,0),
		(case when @Format='S' then 0 else @valoare end),@IDAngajatSalarizare,@Anul,@Luna)

	FETCH NEXT from crsSpor INTO @IDPersoana,@IDAngajatSalarizare,@IDDepartament,@IDTipSpor,@salariu,@baze,@valoare,@Format,@ConcediuOdihna,@OreNORMA,@FractiuneNorma,@IDOcupatie

END
CLOSE crsSpor
DEALLOCATE crsSpor




-- ************************ CAlCUL SPORURI BUGETARI CARE INTRA IN BAZA VECHIME  (intra_in='BV') ******************

DECLARE  crsSpor SCROLL CURSOR FOR 
SELECT DISTINCT 
	Ps.IDPersoana,Ans.IDAngajatSalarizare,Ans.IDDepartament,pss.IDTipSpor,Ans.Salar,
	ts.BazaCalculSpor,ts.valoare,ts.Format,ts.ConcediuOdihna,Ans.OreNorma,Ans.FractiuneNorma,AnO.IDOcupatie
	FROM  	
	    [Persoane] Ps 
	    JOIN [AngajatiSalarizare] Ans ON Ps.IdPersoana = Ans.IdPersoana
	    JOIN [AngajatiOcupatii] AnO ON Ps.IdPersoana = AnO.IdPersoana
	    JOIN [AngajatiSporuri] Pss ON Ps.IdPersoana = Pss.IdPersoana
		JoiN [TipSpor] ts ON Pss.IDTipSpor = ts.IDTipSpor
	WHERE   
	Ps.CodUnitate=@CodUnitate and ts.IntraIn='BV'  and ts.Format<>'G' AND
	YEAR(Pss.DeLaData)*100+MONTH(Pss.DeLaData)<=@Anul*100+@luna 
	AND (Pss.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(Pss.PanaLaData)*100+MONTH(Pss.PanaLaData))
	AND (Ps.DataPlecarii IS NULL OR @Anul*100+@Luna<=YEAR(Ps.DataPlecarii)*100+MONTH(Ps.DataPlecarii))
	AND (Ps.CodUnitate=@CodUnitate)
	AND(@IDPersoanaSelectata=-1 OR Ps.IDPersoana = @IDPersoanaSelectata)


OPEN crsSpor
FETCH first from  crsSpor INTO @IDPersoana,@IDAngajatSalarizare,@IDDepartament,@IDTipSpor,@salariu,@baze,@valoare,@Format,@ConcediuOdihna,@OreNORMA,@FractiuneNorma,@IDOcupatie

WHILE @@fetch_status = 0  
BEGIN

	SELECT @ore_lucrate=isnull((select sum(Pt.Valoare)
	FROM AngajatiPontaj Pt JOIN [TipPrezenta] Tp  ON Pt.IdTipPrezenta = Tp.IdTipPrezenta 
	WHERE  
	Pt.IdPersoana = @IDPersoana AND 
   	Pt.IdAngajatSalarizare=@IDAngajatSalarizare and
   	Tp.TIP = 'P' AND Pt.Luna=@Luna AND Pt.Anul=@Anul),0)

	SELECT @ZI_ODH=isnull((select sum(pt.valoare)
	FROM AngajatiPontaj Pt JOIN [TipPrezenta] Tp  ON Pt.IdTipPrezenta = Tp.IdTipPrezenta 
	WHERE 
	Pt.IdPersoana = @IDPersoana AND 
	Pt.IdAngajatSalarizare=@IDAngajatSalarizare and
	Tp.TIP = 'O' AND Pt.Luna=@Luna AND Pt.Anul=@Anul),0)
	
	IF @Format='F'		
		SELECT @valoare = Valoare FROM [GrileOcupatii] WHERE [IDTipSpor]=@IdTipSpor AND [IDOcupatie]=@IDOcupatie		
	IF @Format='D'		
		SELECT @valoare = Valoare FROM [GrileOcupatiiDep] WHERE [IDTipSpor]=@IdTipSpor AND [IDOcupatie]=@IDOcupatie AND [IDDepartament]=@IDDepartament

	select @Baza_Spor =(case 
		when @baze='SI' then @salariu 
		when @baze = 'SG' then @salariu+isnull((select sum(s.ValoareInt) from 
		CalculSporuri s join TipSpor ts on s.IdTipSpor=ts.IDTipSpor
				where s.IdPersoana=@IDPersoana and Luna=@Luna AND Anul=@Anul AND
				ts.intrain='SG'),0)	
		when @baze = 'SB' then @salariu+isnull((select sum(s.ValoareInt) from 
		CalculSporuri s join TipSpor ts on s.IdTipSpor=ts.IDTipSpor 
				where s.IdPersoana=@IDPersoana and Luna=@Luna AND Anul=@Anul AND
				ts.intrain='SG'),0)	+ isnull((select sum(s.ValoareInt) from 
		CalculSporuri s join TipSpor ts on s.IdTipSpor=ts.IDTipSpor
				where s.IdPersoana=@IDPersoana and Luna=@Luna AND Anul=@Anul AND
				ts.intrain='SB'),0)	

		else	0
	end)	

	IF @ConcediuOdihna=1 
	begin
		set @valoare_spor=@Baza_Spor*(@ore_lucrate/(@OreNorma*@op*@FractiuneNorma))*@valoare/100
		set @valoare_spor_o=@Baza_Spor*(@ZI_ODH/(@op*@FractiuneNorma))*@valoare/100
	end
	ELSE
	begin
		set @valoare_spor=@Baza_Spor*@valoare/100
		set @valoare_spor_o=0
	end
		
	if @Format='S'
		IF @ConcediuOdihna=1 
		begin
			set @valoare_spor=@valoare*(@ore_lucrate/(@OreNorma*@op*@FractiuneNorma))
			set @valoare_spor_o=@valoare*(@ZI_ODH/(@op*@FractiuneNorma))
		end
		ELSE
		begin
			set @valoare_spor=@valoare
			set @valoare_spor_o=0
		end
		

	
	select @valoare_spor_int=@Baza_Spor*@valoare/100
	UPDATE AngajatiSporuri SET [Procent]=@valoare, [Valoare]=ROUND(@valoare_spor_int,0) WHERE
		IdPersoana=@IDPersoana AND [IDTipSpor]=@IdTipSpor

	INSERT  INTO CalculSporuri 
		(IdPersoana, IdTipSpor, VALOARE, VALOARECO,VALOAREINT,PROCENT, IDAngajatSalarizare,Anul,Luna) 
     	 VALUES
		(@IDPersoana, @IdTipSpor,round (@valoare_spor,0),round (@valoare_spor_o,0), round (@valoare_spor_int,0),
		(case when @Format='S' then 0 else @valoare end),@IDAngajatSalarizare,@Anul,@Luna)

	FETCH NEXT from crsSpor INTO @IDPersoana,@IDAngajatSalarizare,@IDDepartament,@IDTipSpor,@salariu,@baze,@valoare,@Format,@ConcediuOdihna,@OreNORMA,@FractiuneNorma,@IDOcupatie

END
CLOSE crsSpor
DEALLOCATE crsSpor


-- ************************ CAlCUL SPORURI BUGETARI CARE NU INTRA NICIUNDE   (intra_in este null) ******************


DECLARE  crsSpor SCROLL CURSOR FOR 
SELECT DISTINCT 
	Ps.IDPersoana,Ans.IDAngajatSalarizare,Ans.IDDepartament,pss.IDTipSpor,Ans.Salar,
	ts.BazaCalculSpor,ts.valoare,ts.Format,ts.ConcediuOdihna,Ans.OreNorma,Ans.FractiuneNorma,AnO.IDOcupatie
	FROM  	
	    [Persoane] Ps 
	    JOIN [AngajatiSalarizare] Ans ON Ps.IdPersoana = Ans.IdPersoana
	    JOIN [AngajatiOcupatii] AnO ON Ps.IdPersoana = AnO.IdPersoana
	    JOIN [AngajatiSporuri] Pss ON Ps.IdPersoana = Pss.IdPersoana
		JoiN [TipSpor] ts ON Pss.IDTipSpor = ts.IDTipSpor
	WHERE   
	Ps.CodUnitate=@CodUnitate and ts.IntraIn is null  and ts.Format<>'G' AND
	YEAR(Pss.DeLaData)*100+MONTH(Pss.DeLaData)<=@Anul*100+@luna 
	AND (Pss.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(Pss.PanaLaData)*100+MONTH(Pss.PanaLaData))
	AND (Ps.DataPlecarii IS NULL OR @Anul*100+@Luna<=YEAR(Ps.DataPlecarii)*100+MONTH(Ps.DataPlecarii))
	AND (Ps.CodUnitate=@CodUnitate)
	AND(@IDPersoanaSelectata=-1 OR Ps.IDPersoana = @IDPersoanaSelectata)


OPEN crsSpor
FETCH first from  crsSpor INTO @IDPersoana,@IDAngajatSalarizare,@IDDepartament,@IDTipSpor,@salariu,@baze,@valoare,@Format,@ConcediuOdihna,@OreNORMA,@FractiuneNorma,@IDOcupatie

WHILE @@fetch_status = 0  
BEGIN

	SELECT @ore_lucrate=isnull((select sum(Pt.Valoare)
	FROM AngajatiPontaj Pt JOIN [TipPrezenta] Tp  ON Pt.IdTipPrezenta = Tp.IdTipPrezenta 
	WHERE  
	Pt.IdPersoana = @IDPersoana AND 
   	Pt.IdAngajatSalarizare=@IDAngajatSalarizare and
   	Tp.TIP = 'P' AND Pt.Luna=@Luna AND Pt.Anul=@Anul),0)

	SELECT @ZI_ODH=isnull((select sum(pt.valoare)
	FROM AngajatiPontaj Pt JOIN [TipPrezenta] Tp  ON Pt.IdTipPrezenta = Tp.IdTipPrezenta 
	WHERE 
	Pt.IdPersoana = @IDPersoana AND 
	Pt.IdAngajatSalarizare=@IDAngajatSalarizare and
	tp.TIP = 'O' AND Pt.Luna=@Luna AND Pt.Anul=@Anul),0)

	select @Baza_Spor =(case 
		when @baze='SI' then @salariu 
		when @baze = 'SG' then @salariu+isnull((select sum(s.ValoareInt) from 
		CalculSporuri s join TipSpor ts on s.IdTipSpor=ts.IDTipSpor
				where s.IdPersoana=@IDPersoana and Luna=@Luna AND Anul=@Anul AND
				ts.intrain='SG'),0)	
		when @baze = 'SB' then @salariu+isnull((select sum(s.ValoareInt) from 
		CalculSporuri s join TipSpor ts on s.IdTipSpor=ts.IDTipSpor
				where s.IdPersoana=@IDPersoana and Luna=@Luna AND Anul=@Anul AND
				ts.intrain='SG'),0)	+ isnull((select sum(s.ValoareInt) from 
		CalculSporuri s join TipSpor ts on s.IdTipSpor=ts.IDTipSpor
				where s.IdPersoana=@IDPersoana and Luna=@Luna AND Anul=@Anul AND
				ts.intrain='SB'),0)	

		else	0
	end)	
	
	IF @Format='F'		
		SELECT @valoare = Valoare FROM [GrileOcupatii] WHERE [IDTipSpor]=@IdTipSpor AND [IDOcupatie]=@IDOcupatie		
	IF @Format='D'		
		SELECT @valoare = Valoare FROM [GrileOcupatiiDep] WHERE [IDTipSpor]=@IdTipSpor AND [IDOcupatie]=@IDOcupatie AND [IDDepartament]=@IDDepartament

IF @ConcediuOdihna=1 
	begin
		set @valoare_spor=@Baza_Spor*(@ore_lucrate/(@OreNorma*@op*@FractiuneNorma))*@valoare/100
		set @valoare_spor_o=@Baza_Spor*(@ZI_ODH/(@op*@FractiuneNorma))*@valoare/100
	end
	ELSE
	begin
		set @valoare_spor=@Baza_Spor*@valoare/100
		set @valoare_spor_o=0
	end
		
	if @Format='S'
		IF @ConcediuOdihna=1 
		begin
			set @valoare_spor=@valoare*(@ore_lucrate/(@OreNorma*@op*@FractiuneNorma))
			set @valoare_spor_o=@valoare*(@ZI_ODH/(@op*@FractiuneNorma))
		end
		ELSE
		begin
			set @valoare_spor=@valoare
			set @valoare_spor_o=0
		end


	select @valoare_spor_int=@Baza_Spor*@valoare/100
	UPDATE AngajatiSporuri SET [Procent]=@valoare, [Valoare]=ROUND(@valoare_spor_int,0) WHERE
		IdPersoana=@IDPersoana AND [IDTipSpor]=@IdTipSpor
	INSERT  INTO CalculSporuri 
		(IdPersoana, IdTipSpor, VALOARE, VALOARECO,VALOAREINT,PROCENT, IDAngajatSalarizare,Anul,Luna) 
     	 VALUES
		(@IDPersoana, @IdTipSpor,round (@valoare_spor,0),round (@valoare_spor_o,0), round (@valoare_spor_int,0),
		(case when @Format='S' then 0 else @valoare end),@IDAngajatSalarizare,@Anul,@Luna)

	FETCH NEXT from crsSpor INTO @IDPersoana,@IDAngajatSalarizare,@IDDepartament,@IDTipSpor,@salariu,@baze,@valoare,@Format,@ConcediuOdihna,@OreNORMA,@FractiuneNorma,@IDOcupatie

END
CLOSE crsSpor
DEALLOCATE crsSpor



-- --------------------------------------- CALCUL SPOR VECHIME  BUGETARI --------------------------------------------------
--- Format = G spor vechime

DECLARE @datavechimemunca datetime
DECLARE @zilesuspCM int
DECLARE  crsSpor SCROLL CURSOR FOR 
    SELECT DISTINCT 
	Ps.IDPersoana,Ans.IDAngajatSalarizare,Ans.IDDepartament,pss.IDTipSpor,Ans.Salar,Ps.DataVechimeMunca,
	isnull((select sum(p.valoare) from AngajatiPontaj p JOIN TipPrezenta tp ON tp.IDTipPrezenta = p.IdTipPrezenta
	where p.IDPersoana=Ps.IDPersoana and tp.Tip='N'),0) as z_nem,
	isnull((SELECT SUM(abs(datediff(day,pscm.PanaLaData,pscm.DeLaData))) FROM [PersoaneSuspCM] pscm WHERE pscm.IDPersoana=Ps.IDPersoana),0) as zile_susp,
	ts.BazaCalculSpor,ts.valoare,ts.Format,ts.ConcediuOdihna,Ans.OreNorma,Ans.FractiuneNorma,AnO.IDOcupatie
	FROM  	
	    [Persoane] Ps 
	    JOIN [AngajatiSalarizare] Ans ON Ps.IdPersoana = Ans.IdPersoana
	    JOIN [AngajatiSporuri] Pss ON Ps.IdPersoana = Pss.IdPersoana
	    JOIN [AngajatiOcupatii] AnO ON Ps.IdPersoana = AnO.IdPersoana
		JoiN [TipSpor] ts ON Pss.IDTipSpor = ts.IDTipSpor
	WHERE   
	Ps.CodUnitate=@CodUnitate AND ts.Format='G' AND
	YEAR(Pss.DeLaData)*100+MONTH(Pss.DeLaData)<=@Anul*100+@luna 
	AND (Pss.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(Pss.PanaLaData)*100+MONTH(Pss.PanaLaData))
	AND (Ps.DataPlecarii IS NULL OR @Anul*100+@Luna<=YEAR(Ps.DataPlecarii)*100+MONTH(Ps.DataPlecarii))
	AND (Ps.CodUnitate=@CodUnitate)
	AND(@IDPersoanaSelectata=-1 OR Ps.IDPersoana = @IDPersoanaSelectata)


OPEN crsSpor
FETCH first from  crsSpor INTO @IDPersoana,@IDAngajatSalarizare,@IDDepartament,@IDTipSpor,@salariu,@datavechimemunca,@ZI_NEMO,@zilesuspCM,@baze,@valoare,@Format,@ConcediuOdihna,@OreNORMA,@FractiuneNorma,@IDOcupatie
DECLARE @ultimaziLuna datetime
SET @ultimaziLuna = DATEADD(DAY,-1,
						DATEADD(MONTH,1,
							CAST(CAST(@anul as varchar(4)) + '-' + CAST(@luna as varchar(2)) + '-01' as datetime)
						)
					)


WHILE @@fetch_status = 0  
BEGIN
	select @AA_VECH=DATEDIFF(year,DATEADD(day,@zilesuspCM,@datavechimemunca),@ultimaziLuna) 
	SET @prcvech=-1
	
	IF 	@aa_vech < 0
	BEGIN
		SET @MesajEroare = 'Vechime negativa adica data de la care se calculeaza vechimea este ulterioara lunii curente [10002] (transa = ' + 
			CAST(@AA_Vech as varchar(20)) + ' datavechime=' + CAST(@datavechimemunca as varchar(100)) + 
			',zile suspendare=' + CAST(@zilesuspCM as varchar(10)) +', idPersoana={'+CAST(@IDPersoana as varchar(20))+'})'
		GOTO EROARE2
	END
	
	SELECT @prcVech = Valoare/100 
		FROM Grile g
		WHERE 
			g.IDTipSpor = @IdTipSpor AND
			(Minim <= @AA_VECH AND  @AA_VECH < Maxim) OR (Minim <= @AA_VECH AND  Maxim IS NULL)
			
	IF 	@prcVech = -1
	BEGIN
		SET @MesajEroare = 'Grila de vechime nu a fost definite pentru toate transele [10001] (transa = ' + 
			CAST(@AA_Vech as varchar(20)) + ' datavechime=' + CAST(@datavechimemunca as varchar(100)) + 
			',zile suspendare=' + CAST(@zilesuspCM as varchar(10)) +', idPersoana='+CAST(@IDPersoana as varchar(20))+')'
		GOTO EROARE2
	END
			
    --PRINT('Vechime=' + CAST(@AA_VECH as varchar(2)))
	SELECT @ore_lucrate=isnull((select sum(Pt.Valoare)
	FROM AngajatiPontaj Pt JOIN [TipPrezenta] Tp  ON Pt.IdTipPrezenta = Tp.IdTipPrezenta 
	WHERE  
	Pt.IdPersoana = @IDPersoana AND 
   	Pt.IdAngajatSalarizare=@IDAngajatSalarizare and
   	Tp.TIP = 'P' AND Pt.Luna=@Luna AND Pt.Anul=@Anul),0)

	SELECT @ZI_ODH=isnull((select sum(pt.valoare)
	FROM AngajatiPontaj Pt JOIN [TipPrezenta] Tp  ON Pt.IdTipPrezenta = Tp.IdTipPrezenta 
	WHERE 
	Pt.IdPersoana = @IDPersoana AND 
	Pt.IdAngajatSalarizare=@IDAngajatSalarizare and
	tp.TIP = 'O' AND Pt.Luna=@Luna AND Pt.Anul=@Anul),0)
--aduna la baza vechime toate sporurile care intra in calculul vechimii
	Select @BazaVech=@salariu+
		isnull((select sum(s.ValoareInt) from 
		CalculSporuri s join TipSpor ts on s.IdTipSpor=ts.IDTipSpor
				where s.IdPersoana=@IDPersoana and Luna=@Luna AND Anul=@Anul AND
				ts.intrain='SG'),0)	+		
		isnull((select sum(s.ValoareInt) from 
		CalculSporuri s join TipSpor ts on s.IdTipSpor=ts.IDTipSpor
				where s.IdPersoana=@IDPersoana and Luna=@Luna AND Anul=@Anul AND
				ts.intrain='SB'),0)	
		/*+
		isnull((select sum(s.ValoareInt) from 
		CalculSporuri s join TipSpor ts on s.IdTipSpor=ts.IDTipSpor
				where s.IdPersoana=@IDPersoana and
				ts.intrain='BV'),0)*/	
				
				
	--pondereaza sporul de vechime cu timpul efectiv lucrat
	Select @SporVechime = @BazaVech*(@ore_lucrate/(@OreNorma*@op*@FractiuneNorma))*@prcVech
	Select @Valoare_spor_o=@BazaVech*(@ZI_ODH/(@op*@FractiuneNorma))*@prcVech
	Select @Valoare_spor_int=@BazaVech*@prcVech
	--seteaza sporul in personal spor
	UPDATE AngajatiSporuri SET [Procent]=@prcVech*100, [Valoare]=ROUND(@valoare_spor_int,0) WHERE
	IdPersoana=@IDPersoana AND [IDTipSpor]=@IdTipSpor
	
	if @ZI_NEMO<>0
			Select @SporVechime=0
	INSERT  INTO CalculSporuri (IdPersoana, IdTipSpor, VALOARE, VALOARECO,VALOAREINT,PROCENT, IDAngajatSalarizare,Anul,Luna) 
     	 VALUES
		(@IDPersoana, @IdTipSpor,
		 round (@SporVechime,0), round (@Valoare_spor_o,0),  round (@Valoare_spor_int,0),
		 @prcVech*100,@IDAngajatSalarizare,@Anul,@Luna)

	FETCH NEXT from crsSpor INTO @IDPersoana,@IDAngajatSalarizare,@IDDepartament,@IDTipSpor,@salariu,@datavechimemunca,@ZI_NEMO,@zilesuspCM,@baze,@valoare,@Format,@ConcediuOdihna,@OreNORMA,@FractiuneNorma,@IDOcupatie
END
CLOSE crsSpor
DEALLOCATE crsSpor



GOTO Gata
EROARE:
	RAISERROR ('Eroare la recalcularea salariilor!',16,-1)
	GOTO Gata
EROARE1:
	RAISERROR ('!!',16,-1)	
	GOTO Gata
EROARE2:
	RAISERROR (@MesajEroare,16,5)	
	GOTO Gata
	
Gata: